package com.yingside.jdbc.gui;

import com.yingside.jdbc.Teacher;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TeacherDao {
    public List<Teacher> list(){
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn = null;
        Statement stmt = null;
        //结果集(游标)
        ResultSet rs = null;
        String url = "jdbc:mysql://47.108.158.110:3306/j181db";
        String user = "root";
        String password = "123456";

        List<Teacher> list = new ArrayList<Teacher>();

        try {
            //3.获取连接
            conn = DriverManager.getConnection(url,user,password);

            //4.获取stmt对象
            stmt = conn.createStatement();

            //5.执行sql语句
            String sql = "select teacher_id,teacher_name,teacher_tel,teacher_birthday,teacher_sex,teacher_age  from t_teacher";

            //查询是单独的一个方法executeQuery
            //查询成功会返回游标
            rs = stmt.executeQuery(sql);

            while(rs.next()){
                int teacher_id = rs.getInt("teacher_id");
                String teacher_name = rs.getString("teacher_name");
                String teacher_tel = rs.getString("teacher_tel");
                String teacher_birthday = rs.getString("teacher_birthday");
                String teacher_sex = rs.getString("teacher_sex");
                int teacher_age = rs.getInt("teacher_age");

                Teacher t = new Teacher(teacher_id,teacher_name,teacher_tel,teacher_birthday,teacher_sex,teacher_age);
                list.add(t);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {

            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return list;
    }


    public int delete(int id){
        int i = 0;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }

        Connection conn = null;
        Statement stmt = null;
        String url = "jdbc:mysql://47.108.158.110:3306/j181db";
        String user = "root";
        String password = "123456";

        try {
            //3.获取连接
            conn = DriverManager.getConnection(url,user,password);

            //4.获取stmt对象
            stmt = conn.createStatement();

            //5.执行sql语句
            String sql = "delete from t_teacher where teacher_id=" + id;
            //增删改都是对数据库的更新，所以调用的都是executeUpdate方法，返回int的值
            //表示对数据库几行受影响
            i = stmt.executeUpdate(sql);

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return i;
    }
}
